package com.dinghui.salary.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.dinghui.salary.mapper.SalaryTypeMapper;
import com.dinghui.salary.pojo.Page;
import com.dinghui.salary.pojo.Salary;
import com.dinghui.salary.pojo.SalaryType;
import com.dinghui.salary.pojo.dto.MouthTypeDTO;
import com.dinghui.salary.pojo.dto.SalaryTypeDTO;
import com.dinghui.salary.pojo.vo.SalaryTypeFieldVO;
import com.dinghui.salary.pojo.vo.SalaryTypeVO;
import com.dinghui.salary.pojo.vo.SelectSalaryTypeVO;
import com.dinghui.salary.service.SalaryTypeService;
import com.dinghui.salary.utils.JDBC;
import com.dinghui.salary.utils.PagedGridResult;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


/**
 * @author tanrentao
 * @date 2021-09-06
 * @desc
 */
@Slf4j
@Service(version = "2.0.0")
public class SalaryTypeServiceImpl extends BaseService implements SalaryTypeService {

    @Autowired
    private SalaryTypeMapper salaryTypeMapper;

    @Override
    public int add(SalaryType salaryType) {
        Example example = new Example(SalaryType.class);
        /*example.orderBy("create_time").desc();*/
        Example.Criteria criteria = example.createCriteria();

        if (StringUtils.isNotBlank(salaryType.getFieldDesc())) {
            criteria.andEqualTo("fieldDesc",  salaryType.getFieldDesc());
        }

        if (StringUtils.isNotBlank(salaryType.getFieldName())) {
            criteria.orEqualTo("fieldName",  salaryType.getFieldName());
        }
        criteria.andEqualTo("isDelete",  0);
        PageHelper.startPage(1, 100);
        List<SalaryType> list = salaryTypeMapper.selectByExample(example);
        if(list.size() > 0){
            return -5;
        }else{
            int i = salaryTypeMapper.insertSelective(salaryType);
            return i;
        }
    }
    public int modify(SalaryType salaryType) {
        Example example = new Example(SalaryType.class);
        example.createCriteria().andEqualTo("id", salaryType.getId());

        Example example1 = new Example(SalaryType.class);
        Example.Criteria criteria1 = example1.createCriteria();

        if (StringUtils.isNotBlank(salaryType.getFieldDesc())) {
            criteria1.andEqualTo("fieldDesc", salaryType.getFieldDesc());
        }

        if (StringUtils.isNotBlank(salaryType.getFieldName())) {
            criteria1.orEqualTo("fieldName", salaryType.getFieldName());
        }

        PageHelper.startPage(1, 10);
        List<SalaryType> list = salaryTypeMapper.selectByExample(example1);
        if (list.size() > 0) {
            for (SalaryType item : list) {
                if (!item.getId().equals(salaryType.getId())) {
                    return -5;
                }else{
                    int i = salaryTypeMapper.updateByExampleSelective(salaryType, example);
                    return i;
                }
            }
        } else {
            int i = salaryTypeMapper.updateByExampleSelective(salaryType, example);
            return i;
        }
        return 0;
    }

        @Override
        public int Delete (SalaryType dto){
            Example example = new Example(SalaryType.class);
            example.createCriteria().andEqualTo("id", dto.getId());
            int i = salaryTypeMapper.updateByExampleSelective(dto, example);
            return i;
        }

        @Override
        public PagedGridResult FindAll (Page dto){

            Example example = new Example(SalaryType.class);
            example.orderBy("createTime").asc();
            Example.Criteria criteria = example.createCriteria();

            if (StringUtils.isNotBlank(dto.getFieldName())) {
                criteria.andLike("fieldName", "%" + dto.getFieldName() + "%");
            }

            if (StringUtils.isNotBlank(dto.getFieldDesc())) {
                criteria.andLike("fieldDesc", "%" + dto.getFieldDesc() + "%");
            }
            criteria.andEqualTo("isDelete",  0);
//            criteria.andEqualTo("isDelete", false);

        /*if (UserStatus.isUserStatusValid(status)) {
            criteria.andEqualTo("activeStatus", status);
        }*/

        /*if (dto.getStartDate() != null) {
            criteria.andGreaterThanOrEqualTo("createTime", dto.getStartDate());
        }
        if (dto.getEndDate() != null) {
            criteria.andLessThanOrEqualTo("createTime", dto.getEndDate());
        }*/

            PageHelper.startPage(dto.getPage(), dto.getPageSize());
            List<SalaryType> list = salaryTypeMapper.selectByExample(example);

            return setterPagedGrid(list, dto.getPage());
        }

        @Override
        public Object FindById (SalaryType dto){
            return salaryTypeMapper.selectOne(dto);
        }


        @Override
        public List<SelectSalaryTypeVO> selectSalaryTypeList () {
            Example example = new Example(SalaryType.class);
            Example.Criteria criteria = example.createCriteria();
            criteria.andEqualTo("isDelete", false);
            List<SalaryType> salaryTypeList = salaryTypeMapper.selectByExample(example);

            List<SelectSalaryTypeVO> list = salaryTypeList.stream()
                    .map(salaryType -> {
                        SelectSalaryTypeVO salaryTypeVO = new SelectSalaryTypeVO();
                        BeanUtils.copyProperties(salaryType, salaryTypeVO);
                        return salaryTypeVO;
                    }).collect(Collectors.toList());

            return list;
        }

        @Override
        public List<SelectSalaryTypeVO> getList (MouthTypeDTO dto){
            String month = dto.getTime();
            List<SelectSalaryTypeVO> list = salaryTypeMapper.selectList(month);
            return list;
        }

        @Override
        public PagedGridResult getAll (Page dto){
            List list = new ArrayList();
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try {
                // 获取连接
                conn = DriverManager.getConnection("jdbc:mysql://192.168.200.111:3306/dh_salary", "root", "123456");

                // 编写sql
                // String sql = "select * from category";

                // 创建语句执行者
                String sql = "select * FROM salary_type";
                st = conn.prepareStatement(sql);

                rs = st.executeQuery();
                if (rs == null) {
//                return Collections.emptyList();

                }
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                while (rs.next()) {
                    Map rowData = new HashMap();
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(metaData.getColumnName(i), rs.getObject(i));
                    }
                    list.add(rowData);
                }
                return setterPagedGrid(list, 1);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                // JDBCUtils.colseResource(conn, st, rs);
            }
//        return null;
            return null;
        }

    @Override
    public List<SalaryTypeFieldVO> getSalaryFieldAll() {
        Example example = new Example(SalaryType.class);
        example.createCriteria().andEqualTo("isDelete", false);
        List<SalaryType> salaryTypeList = salaryTypeMapper.selectByExample(example);

        List<SalaryTypeFieldVO> list = salaryTypeList.stream()
                .map(salaryType -> {
                    SalaryTypeFieldVO salaryTypeFieldVO = new SalaryTypeFieldVO();
                    BeanUtils.copyProperties(salaryType, salaryTypeFieldVO);
                    return salaryTypeFieldVO;
                })
                .collect(Collectors.toList());
        return list;
    }

    @Transactional
    @Override
    public boolean createTable(String time) {
        Connection conn = null;
        PreparedStatement st = null;
        List<SelectSalaryTypeVO> list = salaryTypeMapper.selectList(time);
        if (list.size() > 0) {
            try {
                String sql = "create table "+ list.get(0).getTable_name()+" (id varchar not null";
                if(list!=null&&list.size()>0){
                    sql+=",";
                    sql+="name varchar(64),";
                    sql+="id_number varchar(64),";
                    sql+="time varchar(20),";
                    sql+="should_pay decimal(10),";
                    sql+="real_wages decimal(10),";
                    sql+="month_type_id varchar(60),";
                    sql+="is_delete bit(1),";
                    sql+="create_by varchar(20),";
                    sql+="create_time datetime,";
                    sql+="update_by varchar(20),";
                    sql+="update_time datetime,";
                    int length = list.size();
                    for(int i =0 ;i<length;i++){
                        //添加字段
                        sql+=list.get(i).getName()+" " +list.get(i).getField_type() +"("+ list.get(i).getField_size()+")";
                        //防止最后一个,
                        if(i<length-1){
                            sql+=",";
                        }
                    }
                }
                //拼凑完 建表语句 设置默认字符集
                sql+=")DEFAULT CHARSET=utf8;";
                System.out.println("建表语句是："+sql);
                String ss = sql;
                conn = JDBC.getConnection("dh_salary");
                st = conn.prepareStatement(sql);
                st.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBC.closeConnection(conn);
                JDBC.closeStatement(st);
            }
        }
        return false;
    }
}
